package com.meiyuetao.myt.sale.web.action;

import com.meiyuetao.myt.sale.entity.OrderCostCount;
import com.meiyuetao.myt.sale.service.OrderCostCountService;
import lab.s2jh.core.annotation.MetaData;
import lab.s2jh.core.pagination.PropertyFilter;
import lab.s2jh.core.service.BaseService;
import lab.s2jh.web.action.BaseController;
import org.apache.struts2.rest.HttpHeaders;
import org.h2.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@MetaData("订单统计管理")
public class OrderCostCountController extends BaseController<OrderCostCount, Long> {

    @Autowired
    private OrderCostCountService orderCostCountService;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    protected BaseService<OrderCostCount, Long> getEntityService() {
        return orderCostCountService;
    }

    @Override
    protected void checkEntityAclPermission(OrderCostCount entity) {
    }

    @Override
    @MetaData("创建")
    public HttpHeaders doCreate() {
        return super.doCreate();
    }

    @Override
    @MetaData("更新")
    public HttpHeaders doUpdate() {
        return super.doUpdate();
    }

    @Override
    @MetaData("保存")
    public HttpHeaders doSave() {
        return super.doSave();
    }

    @Override
    @MetaData("删除")
    public HttpHeaders doDelete() {
        return super.doDelete();
    }

    @Override
    @MetaData("查询")
    public HttpHeaders findByPage() {
        return super.findByPage();
    }

    @MetaData("订单统计")
    public HttpHeaders count() {
        Pageable pageable = PropertyFilter.buildPageableFromHttpRequest(getRequest());
        int pageSize = pageable.getPageSize();
        int pageNum = pageable.getPageNumber();
        int startNum = pageNum * pageSize + 1;
        int endNum = (pageNum + 1) * pageSize + 1;
        String orderTime =  this.getParameter("orderTime", "");
        String provider = this.getParameter("provider", "");
        StringBuilder sql = new StringBuilder();
        sql.append("WITH query AS ( SELECT inner_query.*, ROW_NUMBER () OVER (ORDER BY CURRENT_TIMESTAMP) AS row_nr FROM ( SELECT TOP (:endNum) ");
        StringBuilder sqlCount = new StringBuilder();
        sqlCount.append("CONVERT(VARCHAR(100),o.order_time,23) as orderTime,count(o.sid) as total ,sum(d.price) as commodityPrice , sum(o.postage) as postage ,sum(h.amount) as payAcc,sum(c.cost_price) as commodityCost, sum(o.refund_amount) as backFee ");
        sqlCount.append("FROM iyb_box_order o ");
        sqlCount.append("left join iyb_box_order_detail_commodity d ");
        sqlCount.append("on d.order_sid = o.sid ");
        sqlCount.append("inner join iyb_commodity c ");
        sqlCount.append("on d.commodity_sid = c.sid ");
        sqlCount.append("and c.coins is not null ");
        if (!StringUtils.isNullOrEmpty(provider)) {
            sqlCount.append("inner join iyb_partner p ");
            sqlCount.append("on c.partner_sid = p.sid ");
            sqlCount.append("and p.code=:provider ");
        }
        sqlCount.append("left join iyb_customer_account_history h ");
        sqlCount.append("on h.order_sid = o.sid ");
        sqlCount.append("and h.account_type = 'CURRENT' ");
        sqlCount.append("and h.occur_type= 'CONS' ");
        sqlCount.append("WHERE o.order_status = 'S70CLS' ");
        if (!StringUtils.isNullOrEmpty(orderTime)) {
            sqlCount.append("and o.order_time >=:dateFrom ");
            sqlCount.append("and o.order_time <=:dateTo ");
        }
        sqlCount.append("GROUP BY CONVERT(VARCHAR(100),o.order_time,23) ");
        sqlCount.append("ORDER BY CONVERT(VARCHAR(100),o.order_time,23) DESC ");
        sql.append(sqlCount);
        sql.append(") inner_query ) SELECT orderTime, total, postage, commodityPrice, backFee FROM query ");
        sql.append("WHERE row_nr >= :startNum AND row_nr < :endNum ");

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
        MapSqlParameterSource args = new MapSqlParameterSource();
        if (!StringUtils.isNullOrEmpty(orderTime)) {
            String[] dates = orderTime.split("～");
            String dateFrom = dates[0].trim();
            String dateTo = dates[1].trim();
            args.addValue("dateFrom", dateFrom);
            args.addValue("dateTo", dateTo);
        }
        if (!StringUtils.isNullOrEmpty(provider)) {
            args.addValue("provider", provider);
        }
        args.addValue("orderTime", orderTime);
        args.addValue("startNum", startNum);
        args.addValue("endNum", endNum);

        List<OrderCostCount> datas = namedParameterJdbcTemplate.query(sql.toString(), args, new RowMapper<OrderCostCount>() {
            public OrderCostCount mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                OrderCostCount orderCostCount = new OrderCostCount();
                Date orderTime = rs.getDate("orderTime");
                Integer total = rs.getInt("total");
                BigDecimal postage = rs.getBigDecimal("postage");
                BigDecimal commodityPrice = rs.getBigDecimal("commodityPrice");
                BigDecimal backFee = rs.getBigDecimal("backFee");
                // TODO: 16-4-26
                orderCostCount.setOrderTime(orderTime);
                orderCostCount.setTotal(total);
                orderCostCount.setCommodityPrice(commodityPrice);
                orderCostCount.setPostage(postage);
                orderCostCount.setBackFee(backFee);
                return orderCostCount;
            }
        });

        // 计算总数
        StringBuilder sqlCountSelect = new StringBuilder();
        sqlCountSelect.append("SELECT ");
        sqlCountSelect.append(sqlCount);
        List<OrderCostCount> counts = namedParameterJdbcTemplate.query(sqlCountSelect.toString(), args, new RowMapper<OrderCostCount>() {
            public OrderCostCount mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                OrderCostCount orderCostCount = new OrderCostCount();
                Date orderTime = rs.getDate("orderTime");
                Integer total = rs.getInt("total");
                BigDecimal postage = rs.getBigDecimal("postage");
                BigDecimal commodityPrice = rs.getBigDecimal("commodityPrice");
                BigDecimal backFee = rs.getBigDecimal("backFee");
                // TODO: 16-4-26
                orderCostCount.setOrderTime(orderTime);
                orderCostCount.setTotal(total);
                orderCostCount.setCommodityPrice(commodityPrice);
                orderCostCount.setPostage(postage);
                orderCostCount.setBackFee(backFee);
                return orderCostCount;
            }
        });
        setModel(new PageImpl<OrderCostCount>(datas, pageable, counts.size()));
        return buildDefaultHttpHeaders();
    }

}